package com.eascs.admin.o2o.loan.dao;

import java.util.Date;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import com.eascs.admin.common.dao.BaseO2oDao;
import com.eascs.admin.o2o.internal.form.LoanapplyForm;
import com.eascs.admin.o2o.internal.util.BaseUserUtil;
import com.eascs.admin.o2o.loan.entity.O2oLoanUserEntity;
import com.eascs.admin.o2o.loan.entity.O2oLoanapplyEntity;
import com.eascs.gaczcontract.entity.CustomerBasicInfo;
import com.eascs.workflow.form.SDForm;

/**
 * 
 * @author chenxingwang
 *
 */
@Repository
public class O2oLoanapplyDao extends BaseO2oDao {
	/**
	 * 根据loanId和主表ID 查询人员信息 
	 * @param loanapplyNo
	 * @param loanId
	 * @return O2oLoanUserEntity
	 */
	public O2oLoanUserEntity queryLoanpplyNo(String loanapplyNo,String loanId){
		String sql="select * from o2o_loan_user u where u.loanapply_no=? and loan_id=? and u.sponsor_type is null";
		return this.findEntity(sql, O2oLoanUserEntity.class, loanapplyNo,loanId);
	}
	
	/**
	 * 	@Description:
	 *               <p>
	 *               根据loanId和主表ID 查询人员信息  脱敏
	 *               </p>
	 * @author LMX
	 * @date 2018年3月6日 下午3:06:48
	 */
	public O2oLoanUserEntity queryLoanpplyNo_sensitive(String loanapplyNo,String loanId){
		String sql="select CONCAT(LEFT(u.document_no, 3),'****',RIGHT(u.document_no, 3)) AS document_no,CONCAT(LEFT(u.user_phone, 3),'****',RIGHT(u.user_phone, 3)) AS user_phone from o2o_loan_user u where u.loanapply_no=? and loan_id=? and u.sponsor_type is null";
		return this.findEntity(sql, O2oLoanUserEntity.class, loanapplyNo, loanId);
	}
	
	/**
	 * 内审流程  修改主表
	 * @param loanapplyForm
	 * @param userUtils
	 */
	public void updateLoanapply(LoanapplyForm loanapplyForm,BaseUserUtil userUtils){
		String sql = "update o2o_loanapply ol set ol.buyback_id=?,ol.buyback_code=?,ol.buyback_name=?,ol.ea_mount=?,ol.loan_term=?, ol.repay_mentway=?,"
				+ " ol.managefeerate=?,ol.depositrate=?,ol.risk=?,ol.end_ro=?,ol.end_date=?,"
				+ "ol.payment_type=?,ol.generalrate=?,ol.bank_loan_name=?,ol.loanrate=? where ol.loanapply_no=?";
		this.execute(sql,loanapplyForm.getBuybackId(),loanapplyForm.getBuybackCode(),loanapplyForm.getBuybackName(),
				loanapplyForm.getEaMount(), loanapplyForm.getLoanTerm(), loanapplyForm.getRepayMentway(),
				loanapplyForm.getManagefeerate(), loanapplyForm.getDepositrate(), loanapplyForm.getRisk(),
				userUtils.getUpdateOr(), userUtils.getUpdateTime(), loanapplyForm.getPaymentType(),
				loanapplyForm.getGeneralRate(), loanapplyForm.getBankLoanName(), 
				loanapplyForm.getLoanrate(),loanapplyForm.getLoanapplyNo());
	}
	
	/**
	 * 内审流程  修改主表_编辑内审
	 * @param loanapplyForm
	 * @param userUtils
	 */
	public void updateLoanapply_ns(LoanapplyForm loanapplyForm,BaseUserUtil userUtils){
		String sql = "update o2o_loanapply ol set ol.recommend_type=?, ol.buyback_id=?,ol.buyback_code=?,ol.buyback_name=?,ol.ea_mount=?,ol.loan_term=?, ol.repay_mentway=?,"
				+ " ol.managefeerate=?,ol.depositrate=?,ol.risk=?,ol.end_ro=?,ol.end_date=?,"
				+ "ol.payment_type=?,ol.generalrate=?,ol.bank_loan_name=?,ol.loanrate=?,ol.referrer_no=?,"
				+ "ol.referrer_name=?,ol.commice_recommend=?,ol.guar_antee_way=?"
				+ "where ol.loanapply_no=?";
		this.execute(sql,loanapplyForm.getRecommendType(),loanapplyForm.getBuybackId(),loanapplyForm.getBuybackCode(),loanapplyForm.getBuybackName(),
				loanapplyForm.getEaMount(), loanapplyForm.getLoanTerm(), loanapplyForm.getRepayMentway(),
				loanapplyForm.getManagefeerate(), loanapplyForm.getDepositrate(), loanapplyForm.getRisk(),
				userUtils.getUpdateOr(), userUtils.getUpdateTime(), loanapplyForm.getPaymentType(),
				loanapplyForm.getGeneralRate(), loanapplyForm.getBankLoanName(), 
				loanapplyForm.getLoanrate(),loanapplyForm.getReferrerNo(),
				loanapplyForm.getReferrerName(), loanapplyForm.getCommiceRecommend(), loanapplyForm.getGuarAnteeWay(),
				loanapplyForm.getLoanapplyNo());
	}
	/**
	 * 征信动态 表单  平台财务  修改主表数据
	 * @param sdForm
	 * @return
	 */
	public int updateO2oFinanceExamine(SDForm sdForm) {
		O2oLoanapplyEntity loanapply=this.getById(O2oLoanapplyEntity.class,  sdForm.getLoanapplyNo());
		loanapply.setZxRemark(sdForm.getZxRemark());
		loanapply.setEaReturnedAmount(sdForm.getPaymentAvgAmount());
		loanapply.setEaPurchaseamount( sdForm.getTakeAvgAmount());
		loanapply.setEaCooperationDate(sdForm.getEaTime());
		loanapply.setCustomerSalesamount(sdForm.getCustomerSalesamount());
		loanapply.setLoanapplyNo(sdForm.getLoanapplyNo());
		return this.updateById(loanapply);
	}
	/**
	 * 根据BillCode查询
	 * @param billCode
	 */
	public O2oLoanapplyEntity queryBillCode(String billCode){
		String sql="select * from o2o_loanapply t where t.bill_code=?";
		return this.findEntity(sql, O2oLoanapplyEntity.class, billCode);
	}
	/**
	 * 根据BillCode查询
	 * @param billCode
	 */
	public List<O2oLoanapplyEntity> queryLoanId(String loanapplyNo,String loanId,Date createDate){
		String sql="SELECT * FROM O2O_LOANAPPLY T WHERE T.LOAN_TYPE in('BK','BP','BP_JX','SG','YC','SC') AND T.LOAN_ID=? AND T.LOANAPPLY_NO!=? AND T.CREATE_DATE<=? AND T.WF_TYPE IN ('HN','O','YQ','DC') ORDER BY T.CREATE_DATE DESC";
		return this.findEntitys(sql, O2oLoanapplyEntity.class, loanId,loanapplyNo,createDate);
	}
	
	/**
	 * 通过放款时间和贷款用户查询贷款单
	 * */
	public O2oLoanapplyEntity queryLoanByUserAndLoanDate(String userName,Date loanDate){
		String sql="SELECT l.* FROM o2o_loanapply l,o2o_loan_user u WHERE l.loan_id=u.loan_id AND u.user_full_name=? AND l.payment_time =?";
		return this.findEntity(sql, O2oLoanapplyEntity.class, userName,loanDate);
	}
	
	/**
	 * @Title: findJxbLoanByIdcardNum
	 * @Description: 根据证件号查询送审银行为江西银行，并且没有放款的单据
	 * @author 张庆奇
	 * @param idNum
	 * @return
	 * @return List<O2oLoanapplyEntity>
	 * @throws
	 * @date 2017年6月7日 下午2:42:14
	 */
	public List<O2oLoanapplyEntity> findJxbLoanByIdcardNum(String idNum){

		StringBuffer querySql = new StringBuffer();
		querySql.append(" select oll.bill_code from o2o_loanapply oll left join o2o_loan_user olu"); 
		querySql.append(" on oll.loanapply_no = olu.loanapply_no and oll.loan_id = olu.loan_id"); 
		querySql.append(" where olu.document_no=? and oll.bank_loan_name='JCB' and oll.wf_type in ('NF','S','O','SN','HN','T','DC','YQ')  and olu.sponsor_type is null and olu.spouse_id is null"); 
		return this.findEntitys(querySql.toString(), O2oLoanapplyEntity.class, idNum);
	}
	/**@Description:
	 *               <p>
	 *               修改贷款要素
	 *               </p>
	 * @author LMX
	 * @date 2017年9月7日 上午11:05:14
	 * @version V1.0
	 * @param loanapplyEntity
	 * @return
	 */
	public boolean udpateEntity(O2oLoanapplyEntity loanapplyEntity) {
		if(StringUtils.isBlank(loanapplyEntity.getLoanapplyNo()))
			return false;
		loanapplyEntity.setEndDate(new Date());// 最后更新日期
		return o2oJdbcSession.updateById(loanapplyEntity) > 0 ? true : false;
	}
	/**
	 * 放款更客户所有单的第几笔放款
	 * @param loanid
	 */
	public void updateO2oloanapplyBanknum(String loanid) {
		StringBuffer sql=new StringBuffer();
		sql.append(" UPDATE o2o_loanapply y,( ");
		sql.append(" select @rownum:=@rownum+1 as rownum ,t.loanapply_no ");
		sql.append(" from ( ");
		sql.append(" select @rownum:=0,y.* from o2o_loanapply y ");
		sql.append(" WHERE y.loan_id=?  and y.loan_flg='Y' and y.loan_type not in ('EA','FQ') ORDER BY submit_date ");
		sql.append(" )t ) t1  ");
		sql.append(" set y.bank_num=t1.rownum where y.loanapply_no=t1.loanapply_no ");
		this.execute(sql.toString(), loanid);
	}

	/**@Description:
	 *               <p>
	 *                查询上一次放款时间_小于不等于当前的放款时间
	 *               </p>
	 * @author LMX
	 * @date 2018年1月3日 下午2:44:52
	 */
	public String getLastPaymentTime(String loanapplyNo, String loanId) {
		StringBuffer querySql = new StringBuffer();
		querySql.append(" SELECT MAX(l.payment_time) FROM o2o_loanapply l   "); 
		querySql.append(" WHERE 1=1 AND l.loan_flg='Y' AND l.wf_type IN('O','HN','DC','YQ')  "); 
		querySql.append(" AND l.payment_time <(SELECT CASE WHEN a.payment_time IS NULL THEN now() ELSE a.payment_time END FROM o2o_loanapply a WHERE a.loanapply_no = ?) ");//#排数当前放款时间为空的情况,当前放款时间为空表示这单还未放款，则取当前时间now()
		querySql.append(" AND l.loan_id = ?  "); 
		querySql.append(" ORDER BY l.payment_time DESC "); 
		return this.findString(querySql.toString(),loanapplyNo, loanId);
	}

	/**@Description:
	 *               <p>
	 *               脱敏
	 *               </p>
	 * @author LMX
	 * @date 2018年3月6日 下午3:18:13
	 */
	public CustomerBasicInfo getById_sensitive(String loanId) {
		String sql="select CONCAT(LEFT(b.home_phone, 3),'****',RIGHT(b.home_phone, 3)) AS home_phone,CONCAT(LEFT(b.wechat, 3),'****',RIGHT(b.wechat, 3)) AS wechat, CONCAT(LEFT(b.work_unit, 6), '******') AS work_unit from customer_basic_info b where b.loan_user_id=?";
		return this.findEntity(sql, CustomerBasicInfo.class, loanId);
	}
}
